using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;

namespace CocaCola_Inventory_System.Data_Access
{
    class Common
    {
        public static int GetStockUserId(OdbcConnection con)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;
                com.CommandText = "select value from variables where name like 'Stockid'";
                if (con.State != ConnectionState.Open)
                    con.Open();
                int res = Convert.ToInt32(com.ExecuteScalar().ToString());

                return res;

            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return -1;
            }
        }
        public static string GetSystemEmail(OdbcConnection con)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;
                com.CommandText = "select value from variables where name like 'SystemEmail'";
                if (con.State != ConnectionState.Open)
                    con.Open();
                return com.ExecuteScalar().ToString();

                

            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return "";
            }
        }

        public static string GetSystemSMTP(OdbcConnection con)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;
                com.CommandText = "select value from variables where name like 'SystemSMTP'";
                if (con.State != ConnectionState.Open)
                    con.Open();
                return com.ExecuteScalar().ToString();



            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return "";
            }
        }

        public static bool SetStockUserId(OdbcConnection con,int ID)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;
                com.CommandText = "update variables set value = ? where name like 'Stockid'";
                com.Parameters.Add("@value", OdbcType.Int).Value = ID;
                if (con.State != ConnectionState.Open)
                    con.Open();


                return com.ExecuteNonQuery()==1;

            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return false ;
            }
        }

        public static bool SetSystemEmail(OdbcConnection con, string Email)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;
                com.CommandText = "update variables set value = ? where name like 'SystemEmail'";
                com.Parameters.Add("@value", OdbcType.VarChar).Value = Email;
                if (con.State != ConnectionState.Open)
                    con.Open();


                return com.ExecuteNonQuery() == 1;

            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return false;
            }
        }

        public static bool SetSystemSMTP(OdbcConnection con, string SMTP)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;
                com.CommandText = "update variables set value = ? where name like 'SystemSMTP'";
                com.Parameters.Add("@value", OdbcType.VarChar).Value = SMTP;
                if (con.State != ConnectionState.Open)
                    con.Open();


                return com.ExecuteNonQuery() == 1;

            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return false;
            }
        }

        public static void temp(OdbcConnection con)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;
                com.CommandText = "";
                if (con.State != ConnectionState.Open)
                    con.Open();

            }
            catch (Exception ex)
            {
                Common.ReportError(ex);

            }

        }

        public static void ReportError(Exception ex)
        {
            System.Diagnostics.Trace.WriteLine(ex.Message);
        }

        public static bool Export(System.Data.DataTable table, string filename)
        {

            try
            {
                Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                Workbook work = excel.Application.Workbooks.Add(System.Reflection.Missing.Value);

                int ColumnIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    ColumnIndex++;
                    excel.Cells[1, ColumnIndex] = col.ColumnName;

                }
                int rowIndex = 0;
                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    ColumnIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        ColumnIndex++;
                        excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString();
                    }
                }
                //excel.Visible = true; 
                //Worksheet worksheet = (Worksheet)excel.ActiveSheet; 
                //worksheet.Activate();
                excel.ScreenUpdating = false;
                excel.DisplayAlerts = false;

                work.SaveCopyAs(filename);
                excel.Quit();
                return true;
            }
            catch (Exception ex)
            {

                return false;
            }
        }

        public static bool IsValidEmailAddress(string sEmail)
        {
            if (sEmail == null)
            {
                return false;
            }
            else
            {
                return Regex.IsMatch(sEmail, @"
                ^
                [-a-zA-Z0-9][-.a-zA-Z0-9]*
                @
                [-.a-zA-Z0-9]+
                (\.[-.a-zA-Z0-9]+)*
                \.
                (
                com|edu|info|gov|int|mil|net|org|biz|
                name|museum|coop|aero|pro
                |
                [a-zA-Z]{2}
                )
                $",
                RegexOptions.IgnorePatternWhitespace);
            }
        }
    }
}
